from __future__ import print_function
import cx_Oracle

# the code in this file runs inefficiently
# I try to solve this problem by reduce query times, and rewrite codes in ora_db3.py
# todo: insert verify result to the database

# configuration
db_user = "daidongyang"
db_pwd = "daidongyang"
dsn = "192.168.0.232:1521/wenzhou"

# may need to convert the imsi_deviceid's type according to its type in database type
# this is the case the imsi_deviceid is number in database
imsi_deviceids = range(1, 9)
wifi_deviceids = ["333L2083", "333L0321",
                  "333L0503", "333L1649",
                  "220L1372", "333L2422",
                  "333L3968", "333L0524"]
imsi_wifi_deviceid_dict = dict(zip(imsi_deviceids, wifi_deviceids))
verify_imp_strategy = 2

connection = cx_Oracle.connect(db_user, db_pwd, dsn)


# find related imsi and mac, from t to (t + window_size)
# if mac.intime - t_delta <= imsi.capturetime <= mac.outtime, we get a pair (imsi, mac)
# this function returns [(imsi, mac)]
def get_related_imsi_mac(t, window_size, t_delta, imsi_deviceid):
    imsi_list = get_delimit_imsis(t, window_size, imsi_deviceid)
    wifi_deviceid = imsi_wifi_deviceid_dict[imsi_deviceid]
    mac_list = get_delimit_macs(t, window_size, wifi_deviceid)
    # print(imsi_list)
    # print(mac_list)
    imsi_mac_pair = []
    for imsi_item in imsi_list:
        for mac_item in mac_list:
            # mac_item (mac, intime, outtime)
            # imsi_item (imsi, capturetime)
            if mac_item[1] - t_delta <= imsi_item[1] <= mac_item[2] + t_delta:
                imsi_mac_pair.append((imsi_item[0], mac_item[0]))
    return imsi_mac_pair


# t : the unix time, the start time to find possible related imsi and mac(wifi)
# besides, t + window_size is the end time, and window_size is the length of time to consider
# this function will return all the recorded imsi related to imsi_deviceid from time t to (t + window_size)
# the return variable is [(imsi, capturetime)]
def get_delimit_imsis(t, window_size, imsi_deviceid):
    # imsi_deviceid = int(imsi_deviceid), may need to convert imsi_deviceid type according to
    # its value type in database
    cursor = connection.cursor()

    imsi_sql = "select imsi, capturetime from imsi_data " + \
        "where deviceid = :d_id and capturetime > :start_t and capturetime < :end_t "
    # print(imsi_sql)
    cursor.prepare(imsi_sql)
    r = cursor.execute(None, {'d_id': imsi_deviceid, 'start_t': t, 'end_t': t + window_size})
    imsi_list = list(r)
    cursor.close()
    return imsi_list


# t's format is unix time, this function is similar to get_delimit_imsis
# this function will return all the recorded mac related to wifi_deviceid from time t to (t + window_size)
# this function will return [(mac, intime, outtime)]
def get_delimit_macs(t, window_size, wifi_deviceid):
    cursor = connection.cursor()
    wifi_sql = "select mac, intime, outtime from wifi_data " + \
        "where deviceid = :w_id and " + \
        "((intime > :start_t and intime < :end_t) " \
        "or (outtime > :start_t and outtime < :end_t)) "
    # print(wifi_sql)
    cursor.prepare(wifi_sql)
    r = cursor.execute(None, {'w_id': wifi_deviceid, 'start_t': t, 'end_t': t + window_size})
    mac_list = list(r)
    cursor.close()
    return mac_list


#
def verify_imsi_mac_pairs(imsi_mac_pairs, t_delta):
    # may need more test
    verify_single_func = verify_imsi_mac_pair2
    if verify_imp_strategy == 1:
        verify_single_func = verify_imsi_mac_pair
    verify_items = []
    for imsi, mac in imsi_mac_pairs:
        verify_item = verify_single_func(imsi, mac, t_delta)
        verify_items.append(verify_item)
    return verify_items


# verify a single pair
def verify_imsi_mac_pair(imsi, mac, t_delta):
    collision_times = 0
    imsi_freq = 0
    mac_freq = 0
    for imsi_d_id, wifi_d_id in imsi_wifi_deviceid_dict.items():
        imsi_time_seq = get_imsi_time_seq(imsi, imsi_d_id)
        mac_time_seq = get_mac_time_seq(mac, wifi_d_id)
        collision_times += get_collision_time_from_seq(imsi_time_seq, mac_time_seq, t_delta)
        imsi_freq += len(imsi_time_seq)
        mac_freq += len(mac_time_seq)
    return imsi, mac, collision_times, imsi_freq, mac_freq


# imsi_time_seq : [capturetime]
# mac_time_seq : [(intime, outtime)]
def get_collision_time_from_seq(imsi_time_seq, mac_time_seq, t_delta):
    collision_times = 0
    for capturetime in imsi_time_seq:
        for intime, outtime in mac_time_seq:
            if intime - t_delta <= capturetime <= outtime + t_delta:
                collision_times += 1
    return collision_times


# get a list of capturetime with imsi and deviceid is specified
# this function returns [capturetime]
def get_imsi_time_seq(imsi, imsi_deviceid):
    cursor = connection.cursor()
    sql = "select capturetime from imsi_data where imsi=:imsi and deviceid = :d_id"
    cursor.prepare(sql)
    r = cursor.execute(None, {'imsi': imsi, 'd_id': imsi_deviceid})
    imsi_time_seq = [item[0] for item in r]
    cursor.close()
    return imsi_time_seq


# get a list of (intime, outtime) with mac and deviceid is specified
# this function returns [(intime, outtime)]
def get_mac_time_seq(mac, wifi_deviceid):
    cursor = connection.cursor()
    sql = "select intime, outtime from wifi_data where mac=:mac and deviceid = :d_id"
    cursor.prepare(sql)
    r = cursor.execute(None, {'mac': mac, 'd_id': wifi_deviceid})
    # for item in r:
    #     print(item)
    mac_time_seq = list(r)
    cursor.close()
    return mac_time_seq


# provide another implement compared to function verify_imsi_mac_pair()
# this function query the db less times and does more in memory(however, the problem size may be high)
def verify_imsi_mac_pair2(imsi, mac, t_delta):
    # may need more test
    imsi_time_seq2 = get_imsi_time_seq2(imsi)
    mac_time_seq2 = get_mac_time_seq2(mac)
    collision_times = get_collision_time_from_mix_seq(imsi_time_seq2, mac_time_seq2, t_delta)
    return imsi, mac, collision_times, len(imsi_time_seq2), len(mac_time_seq2)


# imsi_time_mix_seq: [(deviceid, capturetime)]
# mac_time_mix_seq: [(deviceid, intime, outtime)]
def get_collision_time_from_mix_seq(imsi_time_mix_seq, mac_time_mix_seq, t_delta):
    # may need more test
    collision_times = 0
    for imsi_did, capturetime in imsi_time_mix_seq:
        for wifi_did, intime, outtime in mac_time_mix_seq:
            if imsi_wifi_deviceid_dict[imsi_did] == wifi_did and intime - t_delta <= capturetime <= outtime + t_delta:
                collision_times += 1
    return collision_times


# return: [(deviceid, capturetime)]
def get_imsi_time_seq2(imsi):
    # may need more test
    cursor = connection.cursor()
    sql = "select deviceid, capturetime from imsi_data where imsi = :imsi"
    cursor.prepare(sql)
    r = cursor.execute(None, {'imsi': imsi})
    imsi_time_seq2 = list(r)
    cursor.close()
    return imsi_time_seq2


#
def get_mac_time_seq2(mac):
    # may need more test
    cursor = connection.cursor()
    sql = "select deviceid, intime, outtime from wifi_data where mac = :mac"
    cursor.prepare(sql)
    r = cursor.execute(None, {'mac': mac})
    mac_time_seq2 = list(r)
    cursor.close()
    return mac_time_seq2



if __name__ == "__main__":
    # imsi_time_seq = [1, 3, 9, 2, 5]
    # mac_time_seq = [(0, 1), (2, 2)]
    # collision_times = get_collision_time_from_seq(imsi_time_seq, mac_time_seq, 1)
    # print(collision_times)
    # for k, v in imsi_wifi_deviceid_dict.items():
    #     print(k, v)
    # imsi_list = get_delimit_imsis(1500007202, 300, 1)
    # print(imsi_list)
    # print(len(imsi_list))
    # mac_list = get_delimit_macs(1500007202, 300, imsi_wifi_deviceid_dict[1])
    # print(mac_list)
    t = 1500007202
    window_size = 30
    t_delta = 1

    imsi_mac_pairs = get_related_imsi_mac(t, window_size, t_delta, 1)
    print(len(imsi_mac_pairs))
    verify_items = verify_imsi_mac_pairs(imsi_mac_pairs, t_delta)
    for verify_item in verify_items:
        print(verify_item)
    # print(imsi_mac_pair)
    # seq = get_mac_time_seq('2m', imsi_wifi_deviceid_dict[1])
    # print(seq)
    connection.close()
    pass